import com.mysql.cj.jdbc.MysqlDataSource;

import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.LinkedList;
import java.util.List;

public class Sql {
    private String user;
    private String password;
    private String database;
    public PreparedStatement statement;
    private final static String URL = "jdbc:mysql://127.0.0.1:3306/";
    public Sql(String user,String password,String database){
        this.database = database;
        this.user = user;
        this.password = password;
    }
    public Connection getConnection() throws SQLException {
        DataSource dataSource = new MysqlDataSource();
        ((MysqlDataSource)dataSource).setUrl(URL + database);
        ((MysqlDataSource)dataSource).setUser(user);
        ((MysqlDataSource)dataSource).setPassword(password);
        return dataSource.getConnection();
    }
    public Integer insertStatement(Connection connection,Book book) throws SQLException {
        String sql = "insert into book (name,author,type,price,publish,status) values (?,?,?,?,?,?)";
        this.statement = connection.prepareStatement(sql);
        this.statement.setString(1,book.getName());
        this.statement.setString(2,book.getAuthor());
        this.statement.setString(3,book.getType());
        this.statement.setDouble(4,book.getPrice());
        this.statement.setString(5,book.getPublish());
        this.statement.setInt(6,book.getStatus());
        return excute();
    }
    public Integer deleteClass(Connection connection,int id) throws SQLException {
        String sql = "delete from book WHERE id = ?";
        this.statement = connection.prepareStatement(sql);
        this.statement.setInt(1,id);
        return excute();
    }

    public Integer updateStatement(Connection connection,Book book) throws SQLException {
        String sql = "update book set name = ?,author = ?,type = ?,price = ?,publish = ?,status = ? where id = ?";
        this.statement = connection.prepareStatement(sql);
        this.statement.setString(1,book.getName());
        this.statement.setString(2,book.getAuthor());
        this.statement.setString(3,book.getType());
        this.statement.setDouble(4,book.getPrice());
        this.statement.setString(5,book.getPublish());
        this.statement.setInt(6,book.getStatus());
        this.statement.setInt(7,book.getId());
        return excute();
    }
    private Integer excute() throws SQLException {
        Integer n = this.statement.executeUpdate();
        this.statement.close();
        return n;
    }
    public List<Book> showStatement(Connection connection) throws SQLException {
        String sql = "select * from book";
        this.statement = connection.prepareStatement(sql);
        return books();
    }
    public List<Book> selectStatement(Connection connection,String name) throws SQLException {
        String sql = "select * from book where name = ?";
        this.statement = connection.prepareStatement(sql);
        this.statement.setString(1,name);
        return books();
    }
    private List<Book> books() throws SQLException {
        ResultSet resultSet = this.statement.executeQuery();
        List<Book> books = new LinkedList<>();
        while (resultSet.next()) {
            Book book = new Book();
            book.setId(resultSet.getInt("id"));
            book.setName(resultSet.getString("name"));
            book.setAuthor(resultSet.getString("author"));
            book.setType(resultSet.getString("type"));
            book.setPrice(resultSet.getDouble("price"));
            book.setPublish(resultSet.getString("publish"));
            book.setStatus(resultSet.getInt("status"));
            books.add(book);
        }
        resultSet.close();
        this.statement.close();
        return books;
    }


}
